#include "sqlmgr.h"
#include "qsqlerror.h"

#include <QCoreApplication>
#include <QSqlQuery>
#include <QDebug>
#include <QSqlRecord>
#include <QDateTime>

//初始化单例SqlMgr
SqlMgr* SqlMgr::instance = nullptr;

SqlMgr::SqlMgr()
{

}

SqlMgr::~SqlMgr()
{

}

void SqlMgr::init()
{
    //链接数据库
    m_db = QSqlDatabase::addDatabase("QSQLITE");
    QString strDbPath = QCoreApplication::applicationDirPath() + "/db/book.db";
    m_db.setDatabaseName(strDbPath);

    bool ok = m_db.open();
    if(ok) qDebug("db open done");
    else { qDebug("fuck"); }
}

bool SqlMgr::login(QString strUser, QString strPass, int &Userid)
{
    QSqlQuery q(m_db);
    //检查用户的登录是否合法
    QString strSql = QString("select * from user where usrname='%1' and passwd ='%2'").arg(strUser).arg(strPass);
    bool ret = q.exec(strSql);
    if(!ret) {
        qDebug()<< q.lastError().text();
    }
    else
    {
        ret =q.next();
        if(ret) { Userid = q.value(0).toInt(); }
    }
    return ret;
}

QVector<QStringList> SqlMgr::getUser(QString strCondition)
{
    QSqlQuery q(m_db);
    QString strQuery = QString("SELECT * FROM user %1").arg(strCondition);
    QVector<QStringList> vec;
    bool ret = q.exec(strQuery); //执行sql
    if(!ret) {
        qDebug() << q.lastError().text();
    }
    else
    {
        //收集结果
        int colsC = q.record().count();
        QStringList l;
        while(q.next())
        {
            l.clear();
            for(int i = 0; i < colsC; ++i) {
                l << q.value(i).toString();
            }
            // qDebug() << l << "\n";
            vec.push_back(l);
        }
    }
    return vec;
}

//添加用户
void SqlMgr::addUser(QVector<QStringList> v)
{
    //建立sql事务，提高插入效率
    m_db.transaction();
    QSqlQuery q(m_db);
    for(auto it : v)
    {
        QString strQuery = QString("INSERT INTO user VALUES(NULL, '%1', '%2', '%3', '%4', '%5', '%6');")
            .arg(it[0])
            .arg(it[1])
            .arg(it[2])
            .arg(it[3])
            .arg(it[4])
            .arg(it[5]);
        bool ret = q.exec(strQuery);
        if(!ret) { qDebug() << q.lastError(); }
    } //提交sql事务
    m_db.commit();
}

void SqlMgr::delUser(QString strId)
{
    QSqlQuery q(m_db);
    QString strQuery = QString("DELETE FROM user WHERE usrid = '%1'").arg(strId);
    bool ret = q.exec(strQuery);
    if(!ret) {
        qDebug() << q.lastError().text();
    }
}

QVector<QStringList> SqlMgr::getBooks(QString strCondition)
{
    QSqlQuery q(m_db);
    QString strQuery = QString("SELECT * FROM book WHERE bookid LIKE '%%1%'").arg(strCondition);
    QVector<QStringList> vec;
    bool ret = q.exec(strQuery); //执行sql
    if(!ret) {
        qDebug() << q.lastError().text();
    }
    else
    {
        //收集结果
        int colsC = q.record().count();
        QStringList l;
        while(q.next())
        {
            l.clear();
            for(int i = 0; i < colsC; ++i) {
                l << q.value(i).toString();
            }
            // qDebug() << l << "\n";
            vec.push_back(l);
        }
    }
    return vec;
}

//新增图书
void SqlMgr::addBook(QVector<QStringList> vec)
{
    if(vec.size() == 0) { return; }
    auto ldata = vec[0];
    QSqlQuery q(m_db);
    QString strQuery = QString("INSERT INTO book "
                               "VALUES(null, '%1', '%2', '%3', '%4', '%5', %6, '')")
                           .arg(ldata[1])
                           .arg(ldata[2])
                           .arg(ldata[3])
                           .arg(ldata[4])
                           .arg(ldata[5])
                           .arg(ldata[6]);
    bool ret = q.exec(strQuery);
    if(!ret) {
        qDebug() << q.lastError().text();
    }
}

//修改图书
void SqlMgr::modifyBook(QStringList ldata)
{
    QSqlQuery q(m_db);
    QString strQuery = QString("UPDATE book "
                               "set bookname ='%1', bookpress='%2',type1='%3',type2='%4',type3='%5',cnt=%6 "
                               "where bookid=%7")
                           .arg(ldata[1])
                           .arg(ldata[2])
                           .arg(ldata[3])
                           .arg(ldata[4])
                           .arg(ldata[5])
                           .arg(ldata[6])
                           .arg(ldata[0]);
    bool ret = q.exec(strQuery);
    if(!ret) {
        qDebug() << q.lastError().text();
    }
}

QString SqlMgr::delBook(QString strId)
{
    QString strRet;
    QSqlQuery q(m_db);
    QString strSql = QString("delete from book where bookid = %1").arg(strId);
    bool ret = q.exec(strSql);
    if(!ret)
    {
        qDebug()<< q.lastError().text();
        strRet ="删除失败,请检查";
    }
    return strRet;
}

//归还图书
QString SqlMgr::returnBook(QString strUsrId, QString strBookId)
{
    QSqlQuery q(m_db);
    QString strSql = QString("update book set cnt = cnt+1 where bookid= %1;").arg(strBookId);
    bool ret = q.exec(strSql);
    if(!ret) {
        qDebug()<< q.lastError().text();
    }
    strSql = QString( "delete from record where bookid = %1 and usrid = %2").arg(strBookId).arg(strUsrId);
    ret = q.exec(strSql);
    if(!ret) {
        qDebug()<< q.lastError().text();
    }
    return QString("");
}

QString SqlMgr::borrowBook(QString strUsrId, QString strBookId)
{
    QSqlQuery q(m_db);

    QString strSql = QString("update book set cnt = cnt-1 where bookid=%1;").arg(strBookId);

    bool ret = q.exec(strSql);
    if(!ret)
    {
        qDebug()<< q.lastError().text();
    }
    strSql = QString( "insert into record VALUES(null,%1,%2,'%3','%4')")
                 .arg(strUsrId)
                 .arg(strBookId)
                 .arg(QDateTime::fromSecsSinceEpoch(QDateTime::currentSecsSinceEpoch()).toString("yyyy-MM-dd hh:mm:ss"))
                 .arg(QDateTime::fromSecsSinceEpoch(QDateTime::currentSecsSinceEpoch()+3600*24*10).toString("yyyy-MM-dd hh:mm:ss"));
    ret = q.exec(strSql);
    if(!ret)
    {
        qDebug()<< q.lastError().text();
    }
    return QString("");
    return QString("");
}

QVector<QStringList> SqlMgr::getRecord(QString strCondition)
{
    QSqlQuery q(m_db);
    QString strQuery = QString("SELECT * FROM record WHERE id LIKE '%%1%'").arg(strCondition);
    QVector<QStringList> vec;
    bool ret = q.exec(strQuery); //执行sql
    if(!ret) {
        qDebug() << q.lastError().text();
    }
    else
    {
        //收集结果
        int colsC = q.record().count();
        QStringList l;
        while(q.next())
        {
            l.clear();
            for(int i = 0; i < colsC; ++i) {
                l << q.value(i).toString();
            }
            // qDebug() << l << "\n";
            vec.push_back(l);
        }
    }
    return vec;
}

void SqlMgr::clearRecord()
{
    //清空表中的数据
    QSqlQuery q(m_db);
    QString strQuery = QString("DELETE FROM record");
    bool ret = q.exec(strQuery);
    if(!ret) {
        qDebug() << q.lastError().text();
    }
    //取消主键自增
    strQuery = QString("DELETE FROM sqlite_sequence WHERE name = 'record'");
    ret = q.exec(strQuery);
    if(!ret) {
        qDebug() << q.lastError().text();
    }
}


